﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Partial Class RenTransfer
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        Else
            lblStaffUID.Text = Session("StaffId").ToString
        End If

        If Not Page.IsPostBack Then
            getddlRen("FROM")
            getddlRen("TO")
        End If
    End Sub

    Private Sub getddlRen(ByVal Type As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT StaffId, Name FROM AW_Staff WHERE StatusDelete = 0 ORDER BY Name"
        objDB.FillData(TempDT, "AW_Staff", Database.SQLCommandType.NormalString)

        If Type = "FROM" Then
            With ddlFromRen
                .DataSource = TempDT
                .DataTextField = "Name"
                .DataValueField = "StaffId"
                .DataBind()
                .Items.Insert(0, "-SELECT-")
                .Items(0).Value = ""
            End With
        Else
            With ddlToRen
                .DataSource = TempDT
                .DataTextField = "Name"
                .DataValueField = "StaffId"
                .DataBind()
                .Items.Insert(0, "-SELECT-")
                .Items(0).Value = ""
            End With
        End If

        objDB.CloseConnection()

    End Sub

    Protected Sub chkCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkCategory.SelectedIndexChanged
        If ddlFromRen.SelectedValue = "" Then
            lblMsgError.Text = "Please select REN to process before select category"
            divError.Visible = True
            chkCategory.ClearSelection()
        Else
            getcount()
        End If
       
    End Sub

    Private Sub getcount()
        For Each litem As ListItem In chkCategory.Items
            If Left(litem.Text, 16) = "Property(Lister)" Then
                If litem.Selected Then
                    litem.Text = "Property(Lister)-" & getPropertyListerCount()
                Else
                    litem.Text = "Property(Lister)"
                End If
            End If
            If Left(litem.Text, 27) = "Pending Transaction(Lister)" Then
                If litem.Selected Then
                    litem.Text = "Pending Transaction(Lister)-" & getPendTransListerCount()
                Else
                    litem.Text = "Pending Transaction(Lister)"
                End If
            End If
            If Left(litem.Text, 31) = "Pending Transaction(Transactor)" Then
                If litem.Selected Then
                    litem.Text = "Pending Transaction(Transactor)-" & getPendTransTransactorCount()
                Else
                    litem.Text = "Pending Transaction(Transactor)"
                End If
            End If
            If Left(litem.Text, 29) = "Completed Transaction(Lister)" Then
                If litem.Selected Then
                    litem.Text = "Completed Transaction(Lister)-" & getCompTransListerCount()
                Else
                    litem.Text = "Completed Transaction(Lister)"
                End If
            End If
            If Left(litem.Text, 33) = "Completed Transaction(Transactor)" Then
                If litem.Selected Then
                    litem.Text = "Completed Transaction(Lister)-" & getCompTransTransactorCount()
                Else
                    litem.Text = "Completed Transaction(Lister)"
                End If
            End If
            If Left(litem.Text, 27) = "History Transaction(Lister)" Then
                If litem.Selected Then
                    litem.Text = "History Transaction(Lister)-" & getHisTransTransactorCount()
                Else
                    litem.Text = "History Transaction(Lister)"
                End If
            End If
            If Left(litem.Text, 31) = "History Transaction(Transactor)" Then
                If litem.Selected Then
                    litem.Text = "History Transaction(Transactor)-" & getHisTransTransactorCount()
                Else
                    litem.Text = "History Transaction(Transactor)"
                End If
            End If
        Next
    End Sub

    Private Function getPropertyListerCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(*) as CountLister FROM AW_Property WHERE SListerId=@ListerID or RListerId=@ListerId"
        objDB.AddParameter("ListerId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountLister").ToString()
        End If

        Return count
    End Function

    Private Function getPendTransListerCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountLister FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status IN ('0','1','2') UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status IN ('0','1','2')) a"
        objDB.AddParameter("ListerId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountLister").ToString()
        End If

        Return count
    End Function

    Private Function getPendTransTransactorCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountTransactor FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status IN ('0','1','2') UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status IN ('0','1','2')) a"
        objDB.AddParameter("TransactorId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountTransactor").ToString()
        End If

        Return count
    End Function

    Private Function getCompTransListerCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountLister FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status = '3' UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status = '3') a"
        objDB.AddParameter("ListerId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountLister").ToString()
        End If

        Return count
    End Function

    Private Function getCompTransTransactorCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountTransactor FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status = '3' UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status = '3') a"
        objDB.AddParameter("TransactorId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountTransactor").ToString()
        End If

        Return count
    End Function

    Private Function getHisTransListerCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountLister FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status IN ('4','5') UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE ListerId=@ListerId AND " & _
                                    "Status IN ('4','5')) a"
        objDB.AddParameter("ListerId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountLister").ToString()
        End If

        Return count
    End Function

    Private Function getHisTransTransactorCount() As String
        Dim TempDT As New Data.DataTable
        Dim count As String = "0"
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT COUNT(TransactionId) as CountTransactor FROM ( " & _
                                    "SELECT TransactionId FROM AW_RentTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status IN ('4','5') UNION " & _
                                    "SELECT TransactionId FROM AW_SaleTransaction WHERE TransactorId=@TransactorId AND " & _
                                    "Status IN ('4','5')) a"
        objDB.AddParameter("TransactorId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.FillData(TempDT, "AW_Property", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()

        If TempDT.Rows.Count > 0 Then
            count = TempDT.Rows(0).Item("CountTransactor").ToString()
        End If

        Return count
    End Function

    Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

        objDB.OpenConnection()
        objDB.InitDbTrans()
        objDB.AddParameter("FromRenId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlFromRen.SelectedValue))
        objDB.AddParameter("ToRenId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlToRen.SelectedValue))

        Try
            'Property(Lister)
            If chkCategory.Items("0").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET SListerId=@ToRenId WHERE SListerId=@FromRenId AND SRelistStatus=0"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET RListerId=@ToRenId WHERE RListerId=@FromRenId AND RRelistStatus=0"
                objDB.ExecuteQuery(False)
            End If

            'PendingTrans(Lister)
            If chkCategory.Items("1").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET SListerId=@ToRenId WHERE SListerId=@FromRenId AND SRelistStatus=1"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status IN ('0','1','2')"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET RListerId=@ToRenId WHERE RListerId=@FromRenId AND RRelistStatus=1"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status IN ('0','1','2')"
                objDB.ExecuteQuery(False)
            End If

            'PendingTrans(Transactor)
            If chkCategory.Items("2").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status IN ('0','1','2')"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status IN ('0','1','2')"
                objDB.ExecuteQuery(False)
            End If

            'CompleteTrans(Lister)
            If chkCategory.Items("3").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET SListerId=@ToRenId WHERE SListerId=@FromRenId AND SRelistStatus=2"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status='3'"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_Property SET RListerId=@ToRenId WHERE RListerId=@FromRenId AND RRelistStatus=2"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status='3'"
                objDB.ExecuteQuery(False)
            End If

            'CompleteTrans(Transactor)
            If chkCategory.Items("4").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status='3'"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status='3'"
                objDB.ExecuteQuery(False)
            End If

            'HistoryTrans(Lister)
            If chkCategory.Items("5").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status IN ('4','5')"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET ListerId=@ToRenId WHERE ListerId=@FromRenId AND Status IN ('4','5')"
                objDB.ExecuteQuery(False)
            End If

            'HistoryTrans(Transactor)
            If chkCategory.Items("6").Selected Then
                objDB.dbVariable.SQLString = "UPDATE AW_SaleTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status IN ('4','5')"
                objDB.ExecuteQuery(False)
                objDB.dbVariable.SQLString = "UPDATE AW_RentTransaction SET TransactorId=@ToRenId WHERE TransactorId=@FromRenId AND Status IN ('4','5')"
                objDB.ExecuteQuery(False)
            End If

            objDB.DbTransCommit()
            objDB.CloseConnection()

            lblMsgSuccessful.Text = "All Selected Updated"
            divSuccessful.Visible = True
            DataFieldClear()
        Catch ex As Exception
            If objDB.dbVariable.objConn.State = Data.ConnectionState.Open Then
                objDB.DbTransRollback()
                objDB.CloseConnection()
            End If
            lblMsgError.Text = ex.Message
            divError.Visible = True
        End Try
    End Sub

    Protected Sub ddlFromRen_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlFromRen.SelectedIndexChanged
        getcount()
    End Sub

    Private Sub DataFieldClear()
        ddlFromRen.SelectedIndex = -1
        ddlToRen.SelectedIndex = -1
        chkCategory.ClearSelection()
        getcount()
    End Sub
End Class
